-- @owner: lihongji
-- @date: 2022/7/25
-- @testpoint: 查询pg_sleep结合group by的测试，合理报错

--step1:建表;expect:成功
drop table if exists t_ustore_pg_sleep_0001 cascade;
create table t_ustore_pg_sleep_0001(
COL_1 bigint,
COL_2 TIMESTAMP WITHOUT TIME ZONE,
COL_3 bool,
COL_4 decimal,
COL_5 text,
COL_6 smallint,
COL_7 char(30),
COL_8 double precision,
COL_9 text,
COL_10 character varying(30),
COL_11 bool ,
COL_12 bytea ,
COL_13 real ,
COL_14 numeric ,
COL_15 blob ,
COL_16 integer ,
COL_17 int ,
COL_18 TIMESTAMP WITH TIME ZONE ,
COL_19 binary_integer ,
COL_20 interval day to second ,
COL_21 boolean,
COL_22 nchar(30),
COL_23 BINARY_INTEGER,
COL_24 nchar(100),
COL_25 character(1000),
COL_26 text,
COL_27 float,
COL_28 float8,
COL_29 bigint,
COL_30 TIMESTAMP WITH  TIME ZONE ,
COL_31 TIMESTAMP,
COL_32 blob,
COL_33 interval year to month,
COL_34 character(30),
COL_35 smallint,
COL_36 blob,
COL_37 char(300),
COL_38 float,
COL_39 raw(100),
COL_40 clob ,
COL_41 binary_double,
COL_42 number(6,2),
COL_43 decimal(6,2),
COL_44 varchar2(50),
COL_45 varchar(30),
COL_46 nvarchar2(100),
COL_47 numeric(12,6),
COL_48 nvarchar2(30),
COL_49 date,
COL_50 blob ,
COL_51 integer,
COL_52 binary_double,
COL_53 decimal(12,6),
COL_54 raw(8000),
COL_55 clob,
COL_56 varchar2(8000),
COL_57 SMALLDATETIME,
COL_58 number(12,6),
COL_59 nvarchar2(4000),
COL_60 BYTEA ,
COL_61 BLOB,
COL_62 SMALLDATETIME,
COL_63 BLOB,
COL_64 varchar(1000),
COL_65 date,
constraint to_timestamp13 primary key(COL_1)
) WITH (STORAGE_TYPE=USTORE);

--step2:创建序列;expect:成功
drop sequence if exists v_seq_pg_sleep;
create sequence v_seq_pg_sleep increment by 1 start with 1;

--step3:清空数据;expect:成功
truncate table t_ustore_pg_sleep_0001;

--step4:插入数据;expect:成功
begin
 for i in 1..3 loop
   insert into t_ustore_pg_sleep_0001 values(
   v_seq_pg_sleep.nextval,
   to_timestamp('12-sep-2014'),
   true,
   3.1415926+v_seq_pg_sleep.nextval,
   lpad('abc','5000','a123&^%djgk'),
   i,
   lpad('abc','30','1'),
   i+1.456789445455,
   lpad('abc','5000','a123&^%djgk'),
   lpad('abc','30','b'),
   false,
   lpad('10','5000','0101')::bytea,
   3.1415926+v_seq_pg_sleep.nextval,
   i/4,
   lpad('10','8000','01010')::blob,
   i,
   i,
   to_timestamp('12-sep-2014'),
   '1',
   (INTERVAL '4 5:12:10.222' DAY TO SECOND(3)),
   0,
   rpad('abc','30','e'),
   v_seq_pg_sleep.nextval,
   rpad('abc','100','exc'),
   lpad('abc','1000','a123&^%djgk'),
   lpad('abc','5000','a123&^%djgk'),
   i/4,
   v_seq_pg_sleep.nextval-99,i*3.1415,
   to_timestamp('12-sep-2014'),
   to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),
   lpad('abc','5000','a123')::blob,
   (INTERVAL '12' YEAR),
   rpad('abc','30','&'),
   i,
   lpad('10',5000,'01010')::blob,
   rpad('abc','300','exc'),
   i/2.15,
   rpad('0F',100,'AADB9')::raw,
   lpad('abc','5000','a123%djgk'),
   1.0E+100,3.14+i,i+445.255,
   rpad('abc','30','&'),
   lpad('abc','30','&'),
   rpad('abc','100','&GDsh'),
   125563.141592,
   rpad('abc','30','e'),
   to_timestamp('12-sep-2014'),
   lpad('abc','5000','123')::blob,
   v_seq_pg_sleep.nextval+2,-1.79E+100,98*0.99,lpad('10','8000','01010')::raw,lpad('abc','5000','a123&^%djgk'),rpad('abc','8000','a123&^%djgk'),to_timestamp('12-sep-2014'),25563.1415,lpad('abc','3000','a字符串123&^%djgk'),lpad('10',2000,'01010')::bytea,
   lpad('10','200','01010')::blob,to_timestamp('12-sep-2014'),'010101111111100000000000000',rpad('abc','1000','&GDsh'),to_timestamp('12-Sep-10 12:10:10.123000','DD-Mon-YY HH24:MI:SS.FF')
   );
    end loop;
end;
/

--step5:group by pg_sleep的结果;expect:合理报错
select distinct pg_sleep(COL_1 in COL_1), pg_sleep('ab' in COL_10), pg_sleep('05:12' in COL_20), pg_sleep('09-12' in COL_30), pg_sleep('23' in COL_25)
from t_ustore_pg_sleep_0001 group by pg_sleep(COL_1 in COL_1),pg_sleep('ab' in COL_10),pg_sleep('05:12' in COL_20),pg_sleep('09-12' in COL_30),pg_sleep('23' in COL_25)
order by 1,2,3,4,5;

--step6:清理环境;expect:成功
drop sequence if exists v_seq_pg_sleep;
drop table if exists t_ustore_pg_sleep_0001;